--    Program: 		CAPARIMP_INVOICE_LINE_DIST.sql
--    Description:  Import AR INVOICE LINE DIST information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
    lv_counter		VARCHAR2(100);  
   	lv_FileName		VARCHAR2(100)	:=	'logB.log';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;
	
   l_cust_trx_line_gl_dist_id	number;
                    		                 		
   cursor cur_ar_invoice_line_dist is
      select * from CRST_RA_CUST_TRX_LINE_GL_DIST
      --WHERE CUST_TRX_LINE_GL_DIST_ID = 53669
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   lv_counter := 0;
   
   for cur in cur_ar_invoice_line_dist loop   	
     l_err_msg_tmp := '';
     
     lv_counter := lv_counter + 1;
     lv_UTL_File := UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
     UTL_FILE.PUT_LINE(lv_UTL_File, lv_counter);
	 UTL_FILE.FClose(lv_UTL_File);
	 
	 IF lv_counter = 10000 THEN
	 	COMMIT;
	 	lv_counter := 0;
	 END IF;
	 	
     
     SELECT RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL 
     INTO   l_cust_trx_line_gl_dist_id
     FROM   DUAL; 
     
     update crst_ra_cust_trx_line_gl_dist
     set    cust_trx_line_gl_dist_id_n = l_cust_trx_line_gl_dist_id
     where  cust_trx_line_gl_dist_id = cur.CUST_TRX_LINE_GL_DIST_ID;
     
     begin
       -- import data
       insert into RA_CUST_TRX_LINE_GL_DIST_ALL
       (
          CUST_TRX_LINE_GL_DIST_ID        ,
          CUSTOMER_TRX_LINE_ID            ,
          CODE_COMBINATION_ID             ,
          SET_OF_BOOKS_ID                 ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATED_BY                 ,
          CREATION_DATE                   ,
          CREATED_BY                      ,
          LAST_UPDATE_LOGIN               ,
          PERCENT                         ,
          AMOUNT                          ,
          GL_DATE                         ,
          GL_POSTED_DATE                  ,
          CUST_TRX_LINE_SALESREP_ID       ,
          COMMENTS                        ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          REQUEST_ID                      ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          CONCATENATED_SEGMENTS           ,
          ORIGINAL_GL_DATE                ,
          POST_REQUEST_ID                 ,
          POSTING_CONTROL_ID              ,
          ACCOUNT_CLASS                   ,
          RA_POST_LOOP_NUMBER             ,
          CUSTOMER_TRX_ID                 ,
          ACCOUNT_SET_FLAG                ,
          ACCTD_AMOUNT                    ,
          USSGL_TRANSACTION_CODE          ,
          USSGL_TRANSACTION_CODE_CONTEXT  ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          LATEST_REC_FLAG                 ,
          ORG_ID                          ,
          MRC_ACCOUNT_CLASS               ,
          MRC_CUSTOMER_TRX_ID             ,
          MRC_AMOUNT                      ,
          MRC_GL_POSTED_DATE              ,
          MRC_POSTING_CONTROL_ID          ,
          MRC_ACCTD_AMOUNT                ,
          COLLECTED_TAX_CCID              ,
          COLLECTED_TAX_CONCAT_SEG        ,
          REVENUE_ADJUSTMENT_ID           ,
          REV_ADJ_CLASS_TEMP              ,
          REC_OFFSET_FLAG                  
       )
       values
       (
          l_cust_trx_line_gl_dist_id          ,
          CAPARIMP_MAPPING.GetCustomerTrxLineIDMapping(cur.CUSTOMER_TRX_LINE_ID),
          CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID),
          CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
          cur.LAST_UPDATE_DATE                ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
          cur.CREATION_DATE                   ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.LAST_UPDATE_LOGIN               ,
          cur.PERCENT                         ,
          cur.AMOUNT                          ,
          cur.GL_DATE                         ,
          cur.GL_POSTED_DATE                  ,
          CAPARIMP_MAPPING.GetCustTrxLineSalesIDMapping(cur.CUST_TRX_LINE_SALESREP_ID),
          cur.COMMENTS                        ,
          cur.ATTRIBUTE_CATEGORY              ,
          cur.ATTRIBUTE1                      ,
          cur.ATTRIBUTE2                      ,
          cur.ATTRIBUTE3                      ,
          cur.ATTRIBUTE4                      ,
          cur.ATTRIBUTE5                      ,
          cur.ATTRIBUTE6                      ,
          cur.ATTRIBUTE7                      ,
          cur.ATTRIBUTE8                      ,
          cur.ATTRIBUTE9                      ,
          cur.ATTRIBUTE10                     ,
          cur.REQUEST_ID                      ,
          cur.PROGRAM_APPLICATION_ID          ,
          cur.PROGRAM_ID                      ,
          cur.PROGRAM_UPDATE_DATE             ,
          cur.CONCATENATED_SEGMENTS           ,
          cur.ORIGINAL_GL_DATE                ,
          cur.POST_REQUEST_ID                 ,
          cur.POSTING_CONTROL_ID              ,
          cur.ACCOUNT_CLASS                   ,
          cur.RA_POST_LOOP_NUMBER             ,
          CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.CUSTOMER_TRX_ID),
          cur.ACCOUNT_SET_FLAG                ,
          cur.ACCTD_AMOUNT                    ,
          cur.USSGL_TRANSACTION_CODE          ,
          cur.USSGL_TRANSACTION_CODE_CONTEXT  ,
          cur.ATTRIBUTE11                     ,
          cur.ATTRIBUTE12                     ,
          cur.ATTRIBUTE13                     ,
          cur.ATTRIBUTE14                     ,
          cur.ATTRIBUTE15                     ,
          cur.LATEST_REC_FLAG                 ,
          CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
          cur.MRC_ACCOUNT_CLASS               ,
          CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.MRC_CUSTOMER_TRX_ID),
          cur.MRC_AMOUNT                      ,
          cur.MRC_GL_POSTED_DATE              ,
          cur.MRC_POSTING_CONTROL_ID          ,
          cur.MRC_ACCTD_AMOUNT                ,
          CAPBRIMP_MAPPING.GetCCIDMapping(cur.COLLECTED_TAX_CCID),
          cur.COLLECTED_TAX_CONCAT_SEG        ,
          cur.REVENUE_ADJUSTMENT_ID           ,
          cur.REV_ADJ_CLASS_TEMP              ,
          cur.REC_OFFSET_FLAG                                       
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_INVOICE_LINE_DIST', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
